import os
import mysql.connector
import json
from datetime import datetime
import pandas as pd
import csv

def query_multi_week_chats():
    """查询跨度为多周的客户聊天记录"""
    # 数据库配置
    db_config = {
        'host': os.getenv('MYSQL_HOST', 'localhost'),
        'user': os.getenv('MYSQL_USER', 'root'),
        'password': os.getenv('MYSQL_PASSWORD', 'neo..6555894'),
        'database': os.getenv('MYSQL_DATABASE', 'sales_chat_mvp')
    }
    
    try:
        # 连接数据库
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)
        
        # 查询所有聊天记录
        query = """
        SELECT 
            nickname,
            is_group,
            customer_nickname,
            week_start_time,
            time_span
        FROM 
            chat_history
        ORDER BY 
            nickname, week_start_time
        """
        
        cursor.execute(query)
        records = cursor.fetchall()
        
        if not records:
            print("数据库中没有聊天记录")
            return
        
        # 按客户/群组名称分组
        chats_by_name = {}
        for record in records:
            name = record['nickname']
            if name not in chats_by_name:
                chats_by_name[name] = []
            chats_by_name[name].append(record)
        
        # 找出跨多周的客户
        multi_week_chats = {}
        for name, chats in chats_by_name.items():
            if len(chats) > 1:
                multi_week_chats[name] = chats
        
        # 打印结果
        if not multi_week_chats:
            print("没有找到跨多周的客户")
            return
        
        print(f"找到 {len(multi_week_chats)} 个跨多周的客户/群组：")
        print("=" * 80)
        
        for name, chats in multi_week_chats.items():
            is_group = chats[0]['is_group']
            chat_type = "群组" if is_group else "客户"
            customer_nickname = chats[0]['customer_nickname'] if not is_group else ""
            
            print(f"{chat_type}: {name}")
            if customer_nickname:
                print(f"客户微信昵称: {customer_nickname}")
            print(f"聊天记录跨越 {len(chats)} 周")
            
            # 打印每周的时间范围
            print("时间跨度:")
            for chat in sorted(chats, key=lambda x: x['week_start_time']):
                time_span = json.loads(chat['time_span'])
                start_time = time_span['start_time']
                end_time = time_span['end_time']
                print(f"  {start_time} 至 {end_time}")
            
            print("-" * 80)
        
    except Exception as e:
        print(f"查询数据库时出错: {e}")
    
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

def query_chat_by_username(username):
    """根据用户名查询聊天历史记录
    
    Args:
        username (str): 要查询的用户名或群组名
        
    Returns:
        list: 包含该用户的聊天记录列表
    """
    # 数据库配置
    db_config = {
        'host': os.getenv('MYSQL_HOST', 'localhost'),
        'user': os.getenv('MYSQL_USER', 'root'),
        'password': os.getenv('MYSQL_PASSWORD', 'neo..6555894'),
        'database': os.getenv('MYSQL_DATABASE', 'sales_chat_mvp')
    }
    
    try:
        # 连接数据库
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)
        
        # 只查询chat_history表中的记录
        query = """
        SELECT 
            nickname,
            is_group,
            customer_nickname,
            week_start_time,
            time_span
        FROM 
            chat_history
        WHERE 
            nickname LIKE %s OR customer_nickname LIKE %s
        ORDER BY 
            week_start_time
        """
        
        search_term = f"%{username}%"
        cursor.execute(query, (search_term, search_term))
        records = cursor.fetchall()
        
        if not records:
            print(f"没有找到与 '{username}' 相关的聊天记录")
            return []
        
        print(f"找到与 '{username}' 相关的聊天记录：")
        print("=" * 80)
        
        # 显示每条记录
        for record in sorted(records, key=lambda x: x['week_start_time']):
            is_group = record['is_group']
            chat_type = "群组" if is_group else "客户"
            name = record['nickname']
            customer_nickname = record['customer_nickname'] if not is_group else ""
            
            time_span = json.loads(record['time_span'])
            start_time = time_span['start_time']
            end_time = time_span['end_time']
            
            print(f"{chat_type}: {name}")
            if customer_nickname:
                print(f"客户微信昵称: {customer_nickname}")
            print(f"时间范围: {start_time} 至 {end_time}")
            
            print("-" * 80)
        
        return records
        
    except Exception as e:
        print(f"查询数据库时出错: {e}")
        return []
    
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

def analyze_chat_data():
    """分析聊天数据，不需要数据库连接"""
    chat_dir = 'history_chat/historychat'
    
    # 获取所有Excel文件
    excel_files = [f for f in os.listdir(chat_dir) if f.endswith('.xlsx') and not f.startswith('.~')]
    
    if not excel_files:
        print("没有找到Excel文件")
        return
    
    print(f"找到 {len(excel_files)} 个Excel文件")
    print("=" * 80)
    
    for file in excel_files:
        file_path = os.path.join(chat_dir, file)
        
        try:
            # 尝试使用不同的引擎读取
            try:
                df = pd.read_excel(file_path, engine='openpyxl')
            except Exception:
                try:
                    df = pd.read_excel(file_path, engine='xlrd')
                except Exception as e:
                    print(f"无法读取文件 {file}: {e}")
                    continue
            
            # 检查必要的列是否存在
            required_columns = ['消息时间', '发送人', '消息内容']
            if not all(col in df.columns for col in required_columns):
                print(f"文件 {file} 缺少必要的列，跳过分析")
                continue
            
            # 确保时间列是datetime类型
            df['消息时间'] = pd.to_datetime(df['消息时间'], errors='coerce')
            df = df.dropna(subset=['消息时间'])
            
            if df.empty:
                print(f"文件 {file} 没有有效的时间数据")
                continue
            
            # 获取时间范围
            min_date = df['消息时间'].min()
            max_date = df['消息时间'].max()
            
            # 计算跨越的周数
            days_diff = (max_date - min_date).days
            weeks_diff = days_diff // 7 + 1
            
            # 判断是否为群聊
            is_group = '@chatroom' in file
            chat_type = "群组" if is_group else "客户"
            
            # 提取名称
            parts = file.split(' - ')
            if len(parts) >= 2:
                name = ' - '.join(parts[1:]).replace('.xlsx', '')
                wx_id = parts[0]
            else:
                name = file.replace('.xlsx', '')
                wx_id = ""
            
            print(f"{chat_type}: {name}")
            print(f"微信ID: {wx_id}")
            print(f"时间范围: {min_date.strftime('%Y-%m-%d')} 至 {max_date.strftime('%Y-%m-%d')}")
            print(f"跨越周数: {weeks_diff}")
            
            if weeks_diff > 1:
                print("【多周聊天记录】")
            
            print("-" * 80)
            
        except Exception as e:
            print(f"分析文件 {file} 时出错: {e}")
            print("-" * 80)

def query_chats_by_role(username=None):
    """查询并输出相同角色（用户）的聊天记录，返回所有字段
    
    Args:
        username (str, optional): 要查询的用户名。如果不提供，则查询所有用户。
    """
    # 数据库配置
    db_config = {
        'host': os.getenv('MYSQL_HOST', 'localhost'),
        'user': os.getenv('MYSQL_USER', 'root'),
        'password': os.getenv('MYSQL_PASSWORD', 'neo..6555894'),
        'database': os.getenv('MYSQL_DATABASE', 'sales_chat_mvp')
    }
    
    try:
        # 连接数据库
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)
        
        # 查询聊天记录，获取所有字段
        if username:
            # 如果提供了用户名，只查询该用户的记录
            query = """
            SELECT 
                *
            FROM 
                chat_history
            WHERE 
                nickname LIKE %s OR customer_nickname LIKE %s
            ORDER BY 
                week_start_time
            """
            search_term = f"%{username}%"
            cursor.execute(query, (search_term, search_term))
        else:
            # 否则查询所有记录
            query = """
            SELECT 
                *
            FROM 
                chat_history
            ORDER BY 
                week_start_time
            """
            cursor.execute(query)
        
        records = cursor.fetchall()
        
        if not records:
            print(json.dumps({"error": f"数据库中没有{'与 '+username+' 相关的' if username else ''}聊天记录"}))
            return
        
        # 处理datetime类型，转换为字符串
        for record in records:
            for key, value in record.items():
                if isinstance(value, datetime):
                    record[key] = value.strftime('%Y-%m-%d %H:%M:%S')
        
        # 直接输出JSON格式
        print(json.dumps(records, ensure_ascii=False, indent=2))
        
    except Exception as e:
        print(json.dumps({"error": f"查询数据库时出错: {str(e)}"}))
    
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

def test_query_by_username():
    """测试根据用户名查询聊天记录的功能"""
    print("=" * 80)
    print("测试根据用户名查询聊天记录")
    print("=" * 80)
    
    # 测试用例列表
    test_cases = [
        "郭小羽",  # 假设的客户名
    ]
    
    for username in test_cases:
        print(f"\n开始查询用户: '{username}'")
        print("-" * 60)
        
        # 调用查询函数
        results = query_chat_by_username(username)
        
        # 输出结果统计
        if results:
            print(f"查询完成: 找到 {len(results)} 条与 '{username}' 相关的消息")
        else:
            print(f"查询完成: 没有找到与 '{username}' 相关的消息")
        
        print("=" * 80)

def query_analyzed_chats(limit=10):
    """查询已经有AI分析结果的聊天记录
    
    Args:
        limit (int): 返回的记录数量，默认为10条
        
    Returns:
        list: 包含AI分析结果的聊天记录列表
    """
    # 数据库配置
    db_config = {
        'host': os.getenv('MYSQL_HOST', 'localhost'),
        'user': os.getenv('MYSQL_USER', 'root'),
        'password': os.getenv('MYSQL_PASSWORD', 'neo..6555894'),
        'database': os.getenv('MYSQL_DATABASE', 'sales_chat_mvp')
    }
    
    try:
        # 连接数据库
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)
        
        # 查询ai_analysis不为空的记录
        query = """
        SELECT 
            id,
            nickname,
            customer_nickname,
            ai_analysis,
            analysis_time
        FROM 
            chat_history
        WHERE 
            ai_analysis IS NOT NULL 
            AND ai_analysis != ''
        ORDER BY 
            analysis_time DESC
        LIMIT %s
        """
        
        cursor.execute(query, (limit,))
        records = cursor.fetchall()
        
        if not records:
            print("没有找到包含AI分析结果的聊天记录")
            return []
        
        print(f"找到 {len(records)} 条包含AI分析结果的聊天记录：")
        print("=" * 80)
        
        # 处理datetime类型，转换为字符串
        for record in records:
            if 'analysis_time' in record and isinstance(record['analysis_time'], datetime):
                record['analysis_time'] = record['analysis_time'].strftime('%Y-%m-%d %H:%M:%S')
        
        # 显示每条记录的基本信息和分析结果
        for idx, record in enumerate(records, 1):
            name = record['nickname']
            customer_nickname = record['customer_nickname'] if record['customer_nickname'] else "无"
            analysis_time = record['analysis_time']
            
            print(f"{idx}. ID: {record['id']}")
            print(f"   名称: {name}")
            print(f"   客户昵称: {customer_nickname}")
            print(f"   分析时间: {analysis_time}")
            
            # 尝试解析AI分析结果
            try:
                analysis = json.loads(record['ai_analysis'])
                judgment = analysis.get('判断结果', '未知')
                print(f"   判断结果: {judgment}")
                
                # 如果是核心业务流程分析，显示更多信息
                if judgment == "核心业务流程分析" and "核心业务流程分析" in analysis:
                    core_analysis = analysis["核心业务流程分析"]
                    
                    # 显示客户画像
                    if "客户画像完善" in core_analysis:
                        customer_profile = core_analysis["客户画像完善"]
                        if "画像总结" in customer_profile:
                            print(f"   客户画像: {customer_profile['画像总结']}")
                    
                    # 显示销售阶段
                    if "销售阶段识别" in core_analysis:
                        sales_stage = core_analysis["销售阶段识别"]
                        if "当前阶段" in sales_stage:
                            print(f"   销售阶段: {sales_stage['当前阶段']}")
                    
                    # 显示成交意向
                    if "成交意向评估" in core_analysis:
                        intention = core_analysis["成交意向评估"]
                        if "评分" in intention and "意向级别" in intention:
                            print(f"   成交意向: {intention['评分']}分 ({intention['意向级别']})")
            except json.JSONDecodeError:
                print(f"   AI分析结果格式无效")
            except Exception as e:
                print(f"   解析AI分析结果时出错: {e}")
            
            print("-" * 80)
        
        # 返回记录
        return records
        
    except Exception as e:
        print(f"查询数据库时出错: {e}")
        return []
    
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

def query_analysis_by_type(analysis_type, limit=5):
    """查询特定类型的AI分析结果
    
    Args:
        analysis_type (str): 分析结果类型，如"核心业务流程分析"或"无关信息"
        limit (int): 返回的记录数量，默认为5条
        
    Returns:
        list: 包含指定类型AI分析结果的聊天记录列表
    """
    # 数据库配置
    db_config = {
        'host': os.getenv('MYSQL_HOST', 'localhost'),
        'user': os.getenv('MYSQL_USER', 'root'),
        'password': os.getenv('MYSQL_PASSWORD', 'neo..6555894'),
        'database': os.getenv('MYSQL_DATABASE', 'sales_chat_mvp')
    }
    
    try:
        # 连接数据库
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)
        
        # 查询包含特定类型分析结果的记录
        query = """
        SELECT 
            *
        FROM 
            chat_history
        WHERE 
            ai_analysis IS NOT NULL 
            AND ai_analysis != ''
            AND ai_analysis LIKE %s
        ORDER BY 
            analysis_time DESC
        LIMIT %s
        """
        
        search_term = f'%"判断结果": "{analysis_type}"%'
        cursor.execute(query, (search_term, limit))
        records = cursor.fetchall()
        
        if not records:
            print(f"没有找到包含'{analysis_type}'分析结果的聊天记录")
            return []
        
        print(f"找到 {len(records)} 条包含'{analysis_type}'分析结果的聊天记录：")
        print("=" * 80)
        
        # 处理datetime类型，转换为字符串
        for record in records:
            for key, value in record.items():
                if isinstance(value, datetime):
                    record[key] = value.strftime('%Y-%m-%d %H:%M:%S')
        
        # 显示每条记录的所有字段
        for idx, record in enumerate(records, 1):
            print(f"记录 {idx}:")
            print("-" * 80)
            for key, value in record.items():
                # 对于较长的字段，只显示部分内容
                if key in ['ai_analysis', 'chat_history'] and value and len(value) > 500:
                    print(f"{key}: {value[:200]}... [内容过长，已截断]")
                else:
                    print(f"{key}: {value}")
            print("=" * 80)
        
        # 返回记录
        return records
        
    except Exception as e:
        print(f"查询数据库时出错: {e}")
        return []
    
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

def query_and_parse_core_analysis(limit=5):
    """查询并解析包含"核心业务流程分析"的AI分析结果
    
    Args:
        limit (int): 返回的记录数量，默认为5条
        
    Returns:
        list: 包含解析后的核心业务流程分析结果的列表
    """
    # 数据库配置
    db_config = {
        'host': os.getenv('MYSQL_HOST', 'localhost'),
        'user': os.getenv('MYSQL_USER', 'root'),
        'password': os.getenv('MYSQL_PASSWORD', 'neo..6555894'),
        'database': os.getenv('MYSQL_DATABASE', 'sales_chat_mvp')
    }
    
    try:
        # 连接数据库
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)
        
        # 查询包含"核心业务流程分析"的记录
        query = """
        SELECT 
            id,
            nickname,
            customer_nickname,
            ai_analysis,
            analysis_time
        FROM 
            chat_history
        WHERE 
            ai_analysis IS NOT NULL 
            AND ai_analysis != ''
            AND ai_analysis LIKE %s
        ORDER BY 
            analysis_time DESC
        LIMIT %s
        """
        
        search_term = '%"判断结果": "核心业务流程分析"%'
        cursor.execute(query, (search_term, limit))
        records = cursor.fetchall()
        
        if not records:
            print("没有找到包含'核心业务流程分析'的AI分析结果")
            return []
        
        print(f"找到 {len(records)} 条包含'核心业务流程分析'的AI分析结果：")
        print("=" * 80)
        
        parsed_results = []
        
        # 处理每条记录
        for idx, record in enumerate(records, 1):
            record_id = record['id']
            name = record['nickname']
            customer_nickname = record['customer_nickname'] if record['customer_nickname'] else "无"
            analysis_time = record['analysis_time']
            if isinstance(analysis_time, datetime):
                analysis_time = analysis_time.strftime('%Y-%m-%d %H:%M:%S')
            
            print(f"记录 {idx}:")
            print(f"ID: {record_id}")
            print(f"名称: {name}")
            print(f"客户昵称: {customer_nickname}")
            print(f"分析时间: {analysis_time}")
            print("-" * 80)
            
            # 解析AI分析结果
            try:
                analysis = json.loads(record['ai_analysis'])
                parsed_results.append(analysis)
                
                # 打印解析后的结构化内容
                print("解析后的AI分析结果:"+record['ai_analysis'])
                
                
            except json.JSONDecodeError:
                print("AI分析结果格式无效，无法解析为JSON")
            except Exception as e:
                print(f"解析AI分析结果时出错: {e}")
            
            print("=" * 80)
        
        return parsed_results
        
    except Exception as e:
        print(f"查询数据库时出错: {e}")
        return []
    
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

def extract_core_analysis_tags_to_csv(output_file='core_analysis_tags.csv'):
    """提取包含"核心业务流程分析"判断结果的记录，将nickname和ai_tags导出为CSV
    
    Args:
        output_file (str): 输出CSV文件的路径，默认为'core_analysis_tags.csv'
    """
    # 数据库配置
    db_config = {
        'host': os.getenv('MYSQL_HOST', 'localhost'),
        'user': os.getenv('MYSQL_USER', 'root'),
        'password': os.getenv('MYSQL_PASSWORD', 'neo..6555894'),
        'database': os.getenv('MYSQL_DATABASE', 'sales_chat_mvp')
    }
    
    try:
        # 连接数据库
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)
        
        # 查询包含"核心业务流程分析"的记录，并且排除群组聊天
        query = """
        SELECT 
            id,
            nickname,
            ai_tags,
            ai_judgment,
            is_group
        FROM 
            chat_history
        WHERE 
            ai_judgment LIKE %s
            AND (is_group = 0 OR is_group IS NULL)
        """
        
        search_term = '%"判断结果": "核心业务流程分析"%'
        cursor.execute(query, (search_term,))
        records = cursor.fetchall()
        
        if not records:
            print("没有找到包含'核心业务流程分析'的非群组记录")
            return
        
        print(f"找到 {len(records)} 条包含'核心业务流程分析'的非群组记录")
        
        # 准备CSV数据
        csv_data = [["nickname", "tags"]]
        
        for record in records:
            nickname = record['nickname']
            
            # 处理ai_tags，提取标签值并转换为逗号分隔的字符串
            tags_str = ""
            if record['ai_tags']:
                try:
                    # 尝试解析JSON格式的标签
                    tags = json.loads(record['ai_tags'])
                    
                    if isinstance(tags, dict):
                        # 处理标准格式：{"标签": {"平台标签": [...], "是否成交": "..."}}
                        tags_list = []
                        
                        # 处理标签字典
                        if "标签" in tags and isinstance(tags["标签"], dict):
                            tag_dict = tags["标签"]
                            
                            # 提取平台标签（通常是列表）
                            if "平台标签" in tag_dict and isinstance(tag_dict["平台标签"], list):
                                tags_list.extend(tag_dict["平台标签"])
                            
                            # 提取是否成交标签（通常是字符串）
                            if "是否成交" in tag_dict and tag_dict["是否成交"]:
                                tags_list.append(tag_dict["是否成交"])
                        
                        # 处理其他可能的格式
                        for tag_category, tag_values in tags.items():
                            if tag_category != "标签":  # 避免重复处理
                                if isinstance(tag_values, list):
                                    tags_list.extend(tag_values)
                                elif isinstance(tag_values, str):
                                    tags_list.append(tag_values)
                                elif isinstance(tag_values, dict):
                                    # 处理嵌套字典情况
                                    for _, nested_value in tag_values.items():
                                        if isinstance(nested_value, list):
                                            tags_list.extend(nested_value)
                                        elif isinstance(nested_value, str):
                                            tags_list.append(nested_value)
                        
                        # 去除空字符串并连接
                        tags_list = [tag for tag in tags_list if tag]
                        tags_str = ",".join(tags_list)
                    elif isinstance(tags, list):
                        # 如果是列表格式，直接连接
                        tags_str = ",".join(tags)
                    elif isinstance(tags, str):
                        # 如果已经是字符串，直接使用
                        tags_str = tags
                    
                except json.JSONDecodeError:
                    # 如果不是JSON格式，直接使用原始字符串
                    tags_str = record['ai_tags']
                except Exception as e:
                    print(f"处理记录ID {record['id']} 的标签时出错: {e}")
            
            # 添加到CSV数据
            csv_data.append([nickname, tags_str])
        
        # 写入CSV文件
        with open(output_file, 'w', newline='', encoding='utf-8') as f:
            writer = csv.writer(f)
            writer.writerows(csv_data)
        
        print(f"已将数据导出到 {output_file}")
        
    except Exception as e:
        print(f"处理数据时出错: {e}")
    
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    # 尝试从数据库查询
    # try:
    #     print("尝试从数据库查询跨多周的客户...")
    #     query_multi_week_chats()
    # except Exception as e:
    #     print(f"数据库查询失败: {e}")
    #     print("将直接分析Excel文件...")
    #     analyze_chat_data() 
    
    # 测试根据用户名查询
    # test_query_by_username() 
    
    # 查询相同角色的所有聊天记录
     query_chats_by_role("陈总 奥诚科技A2_16")
    
    # 查询已分析的聊天记录
    # query_analyzed_chats(10)
    
    # 查询包含"核心业务流程分析"的记录
    # print("\n查询包含'核心业务流程分析'的记录:")
    # query_analysis_by_type("核心业务流程分析", 5)
    
    # 查询包含"无关信息"的记录
    # print("\n查询包含'无关信息'的记录:")
    # query_analysis_by_type("无关信息", 5)
    
    # 查询并解析包含"核心业务流程分析"的记录
    # query_and_parse_core_analysis(20)
    
    # 提取核心业务流程分析的标签到CSV
    #extract_core_analysis_tags_to_csv() 